Hi, welcome to this third lesson, on charts and dashboard creation.
In the previous lesson, you explored your first dataset and created your first table chart.
In this lesson, you will keep on exploring the same dataset, create new types of charts and build your first dashboard.
By the end of this lesson,
switzerland.foph_hosp_d) from the Swiss Federal Office of
Public Health (FOPH)Suppose we are interested in following the evolution of weekly
hospitalisations in Switzerland over the last 2 years, and would like to
create a new chart for that, using the
switzerland.foph_hosp_d dataset we started exploring in the
previous lesson.
Let’s go back to the Charts page, and click on the
+CHART button in the upper right corner of the page to
create a new chart.
The page named Create a new chart opens. You must do two things here:
In the field CHOOSE A DATASET, type or select from the drop-down list
again the foph_hosp_d dataset.
In the CHOOSE CHART section, different sub-sections are available: Recommended tags, Category, and Tags.
In the Category sub-section, select the
Evolution category. A number of corresponding charts
will appear on the right panel. Let’s click on the first one and select
the: Line Chart. It is the classic chart that visualised
how metrics change over time.
We can then click on CREATE NEW CHART button in the
bottom.
When you do so, a new Chart page opens, with pre-filled Dataset and Chart type fields.
You can see as well that for this type of chart, some parameters are mandatory. It is the case here for the METRICS field in the Query section. That is why it is colored in red, and annotated with an exclamation mark (!). Indeed, for line charts, METRICS field cannot be empty and one or many several metrics must be selected to be displayed.
As at least one METRIC is mandatory, let’s click on
+ Add metric in the METRICS field. A pop-up window appears.
When we select the SIMPLE menu, we are requested to fill
the COLUMN to be displayed and how we would like to AGGREGATE it.
As we have daily new hospitalisations data in our dataset and we are
interested visualising weekly hospitalisations, we will set COLUMN to
entries, and AGGREGATE to SUM, since the
number of hospitalisations (entries) per week is the SUM of the daily
hospitalisation during that week :)
Based on your definition, this metric is now labeled SUM(entries). Unless you edit it, this is the label that will appear in the legend of your chart.
To edit the label of this metric (e.g. change it to “Number of entries”), click on the pencil icon, highlighted below:
When you are done, click SAVE in this pop-up window,
then click RUN QUERYon the right panel of the Chart page.
The result should be the following:
By default the TIME GRAIN field in the Time section is set to
Day. As we are interested by numbers of weekly
hospitalisations, let’s change it to Week, for aggregating
entries at the weekly-level, and RUN QUERYagain. You will
get the result below, where you can directly see how it made the line
smoother.
Currently all entries in our table (independently from
georegion value) are summed by week. Let’s GROUP
entries BY georegion, to see the evolution of
the number of entries by georegion (Swiss canton).
In GROUP BY field, add georegion
column, and press on RUN QUERY.
The results should look like the following:
Great! We can see now that data are separated by
canton. But, we also see that we have data for the whole Switzerland
(georegion = CH) and for Switzerland and Liechtenstein
(georegion = CHFL) all together.
In order to focus on canton data only, we must FILTER out
CH and CHFL data. In FILTERS, click on
+Add filter, and set the filter to
georegion NOT IN (CH, CHFL).
georegion, add it to see the correspondence
between the line colors and the cantons. For that, go to the CUSTOMIZE
tab (next to DATA tab), and tick the LEGEND box.Note that the plot is interactive; you can show or hide lines from the chart by clicking or double-clicking on their associated legend item!
Time in the X AXIS LABEL field, and
Number of hospitalisations in the Y AXIS LABEL field of the
Y AXIS section.And that’s the result, where we can see the different waves during the last 2 years, by Swiss canton.
Finally, as previously done with Table Chart, let’s:
Evolution of weekly COVID hospitalisations in Switzerland by Canton,COVID Hospitalisations in Geneva and Switzerland),
and click on the SAVE & GO TO DASHBOARD.The dashboard will open and you will see that your two charts (1) table chart and 2) line chart) were added, as below:
You can easily edit the format of the dashboard. To do so, click on the Edit button on the top right corner of the page.
It will open the Edit Dashboard page, below:
There, you can put the charts side by side (with drag and drop) and
resize them to take the whole width of the page. When done,
SAVE it, and get the following NICE result :)
Now, let’s create a last chart. You already know how to create a new chart, right? :)
+CHART button.Suppose we are interested in creating a Map providing an overview of COVID hospitalisations across Switzerland’s cantons during the last month, so:
foph_hosp_d.Country Map in the right
panel.CREATE NEW CHART buttonIn the Query section of the Chart Page, fill :
Switzerland,isocode, andSUM(entries), as we did in the
previous line chart.click on the + symbol next to METRIC, select the SIMPLE
panel, and set COLUMN to entries and AGGREGATE to
SUM.
It should look like the following:
In this dataset, the isocode column allows us to link
the canton data to the corresponding canton polygon on the country map.
This isocode is a concatenation of the
string 'CH-' with the value of georegion
(‘CH’+georegion). For instance, canton Geneva, with
georegion = 'GE', has and
isocode = 'CH-GE'.
As we are interested here by canton data only, let’s again FILTER out
data where georegion = ‘CH’ or
georegion = ‘CHFL’. To do that, we will add a filter
georegion NOT IN (‘CH’,’CHFL’), and RUN QUERY
again, as follows:
If you are not fan of the color scheme, you can change it, by going
to the Chart Options section (under the
Query section), selecting for instance
oranges in the LINEAR COLOR SCHEME field, and
RUN QUERY again, to get the following result:
It does look quite nice :) What do you think?
As we did not edit the time-related elements yet, you have now on your map the total number of hospitalisation entries per canton, since February 2020, while we are interested by last month’s hospitalisations only.
To correct that, we should go to Time section in the
middle panel, and set TIME RANGE to last month. For that,
click on No filter value in the TIME RANGE field; this will
open a pop-up window named Edit time range.
There, select:
Last in RANGE TYPE,last month in Configure Time Range
Last…:, andAPPLY button in the bottom right of the
window. The window will close and you will be able to
RUN QUERY again.Below is the resulting map. Notice that the map is dynamic, when you pass your mouse over the cantons on the map, the name of the canton and the number of hospitalisation entries in this canton appear.
It is now time to :
Number of COVID hospitalisations during last month,+SAVE button in the middle
panel,SAVE & GO TO DASHBOARD.When on the Dashboard page, Edit the format of the dashboard to:
If you have followed all the instructions above, you should get this result:
To publish our dashboard and make it accessible to all on the EpiGraphHub platform, we need to do one last step:
Draft button on the right of the
dashboard’s titleAfter this click, the dashboard shows up as
Published!
Congratulations! You reached the end of this third lesson, where you have created two new types of charts, i.e. a line chart and a country map, and where you completed building your first dashboard.
Hopefully, you are now comfortable with displaying the evolution of quantitative values over time, and mapping geographical data. You should also know how quick it is to combine your different charts into a dashboard that can be easily shared with your colleagues.
While the dataset you have explored for now was made of data already aggregated at the geographical region level, in the next lesson you will explore a new dataset with raw individual data. See you there :)
The following team members contributed to this lesson:
This work is licensed under the Creative Commons Attribution Share Alike license.